package com.asura;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import cn.itcast.jdbc.JdbcUtils;
import cn.itcast.jdbc.TxQueryRunner;

/**
 * 用来获取数据库连接对象
 * 
 * 底层使用c3p0
 * 
 * @author 礼逊
 *
 */
public class JdbcUtilsTest {

	@Test
	public void testGetConnection() throws SQLException{
		Connection conn = JdbcUtils.getConnection();
		System.out.println(conn);
		JdbcUtils.releaseConnection(conn);
		System.out.println(conn.isClosed());
	}
	
	/**
	 * 与事务相关的功能
	 */
	@Test
	public void testTranscation(){
		try {
			JdbcUtils.beginTransaction();
			//多次操作....
			JdbcUtils.commitTransaction();
		} catch (Exception e) {
			try {
				//回滚
				JdbcUtils.rollbackTransaction();
			} catch (SQLException e1) {
				// TODO 自动生成的 catch 块
				e1.printStackTrace();
			}
		}
	}
	@Test
	public void testQueryUpdate() throws Exception{
		String sql = "insert into person (pid,pname,age,sex)values(?,?,?,?);";
		QueryRunner qr = new TxQueryRunner();
		Object[] params = {"2","asura2","33","女"};
		qr.update(sql, params);
	}
	
	/**
	 * 单行结果集放入javaBean中
	 * @throws Exception
	 */
	@Test
	public void testQuery() throws Exception{
		String sql = "select * from person where pid=?";
		QueryRunner qr = new TxQueryRunner();
		Person p = qr.query(sql, new BeanHandler<Person>(Person.class),"1");
		System.out.println(p.toString());
	}
	
	/**
	 * 多行结果集放入javaBean中，产生一个javaBean的List
	 * @throws Exception
	 */
	@Test
	public void testQueryList() throws Exception{
		String sql = "select * from person";
		QueryRunner qr = new TxQueryRunner();
		List<Person> lists = qr.query(sql, new BeanListHandler<Person>(Person.class));
		for(Person list : lists){
			System.out.println(list);
		}
		
	}
	
	/**
	 * 单行结果集映射到map中
	 * @throws Exception
	 */
	@Test
	public void testQueryToMap() throws Exception{
		String sql = "select * from person where pid=?";
		Object[] param = {"2"};
		QueryRunner qr = new TxQueryRunner();
		Map<String,Object> map = qr.query(sql, new MapHandler(),param);
		System.out.println(map);
		
	}
	/**
	 * 多行结果集映射到map中
	 * @throws Exception
	 */
	@Test
	public void testQueryToMapList() throws Exception{
		String sql = "select * from person";
		QueryRunner qr = new TxQueryRunner();
		List<Map<String,Object>> mapList = qr.query(sql, new MapListHandler());
		System.out.println(mapList);
		
	}
	
	/**
	 * ScalarHandler测试
	 * @throws Exception
	 */
	@Test
	public void testScalarHandler() throws Exception{
		String sql = "select count(*) from person";
		QueryRunner qr = new TxQueryRunner();
		Object obj = qr.query(sql, new ScalarHandler());
		/**
		 * 不同的驱动结果不同，所以需要强转为Number类型
		 */
		Number number = (Number)obj;
		int reVal = number.intValue();
		System.out.println(reVal);
		
	}
	
	/**
	 * 多表关联查询测试
	 * @throws Exception
	 */
	@Test
	public void testJoin() throws Exception{
		String sql = "select * from person";
		QueryRunner qr = new TxQueryRunner();
		Object obj = qr.query(sql, new ScalarHandler());
		/**
		 * 不同的驱动结果不同，所以需要强转为Number类型
		 */
		Number number = (Number)obj;
		int reVal = number.intValue();
		System.out.println(reVal);
		
	}
}
